/* -*- Mode: C; indent-tabs-mode: t; c-basic-offset: 4; tab-width: 4 -*- */
/*
 * Copyright (C) chaitanya chandel 2009 <cchandel@yahoo.com>
 * 
 * db_sqlite.c is free software: you can redistribute it and/or modify it
 * under the terms of the GNU General Public License as published by the
 * Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * db_sqlite.c is distributed in the hope that it will be useful, but
 * WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 * See the GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License along
 * with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
 
#include "db_sqlite.h"
#include <id3.h>
#include <vorbis/codec.h>
#include <vorbis/vorbisfile.h>
#include <string.h>

int check_file_exists(char *path) 
{
	struct stat sts;

	if(stat(path, &sts) == -1 && ENOENT) return 0;
	else return 1;
}

void save_state(void)
{
	int db_ret;
	char *err, *sql_st=NULL, *sql;

	//set this up as a transaction
	sqlite3_exec(songs, "BEGIN", 0, 0, 0);

	//clear up the state table
	sql = "DELETE FROM state;";
	db_ret = sqlite3_exec(songs, sql, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
	  if (err != NULL) {
		  //fprintf(stderr, "1 SQL error: %s\n", err);
		  sqlite3_free(err);
	  }
	}
	if (play_state == PAUSE) seek = pos_time;
	else seek = 0;	
	//save cur_playlist, song_index, vol
	sql_st = sqlite3_mprintf("INSERT INTO state(playlist, song_no, volume, seek, dur, loop, finger, random, bass, treble, priority, version, bt) VALUES('%q', %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d);",
							 cur_playlist, song_index, vol, seek, song_dur, loop, finger, randomize, bass, treble, priority, version, bt);
	//printf("%s\n", sql_st);
	db_ret = sqlite3_exec(songs, sql_st, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
	  if (err != NULL) {
		  //fprintf(stderr, "2 SQL error: %s\n", err);
		  sqlite3_free(err);
	  }
	}
	//end transaction
	sqlite3_exec(songs, "COMMIT", 0, 0, 0);
	sqlite3_free(sql_st);
}

void first_run(void)
{
	char *sql, *err;
	int db_ret;

	//create tables
	sql ="CREATE TABLE state(key integer primary key, playlist text, song_no integer, volume integer, seek integer, dur integer, loop integer, finger integer, random integer, bass integer, treble integer, priority integer, version integer, bt integer);";
	db_ret = sqlite3_exec(songs, sql, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
		if (err != NULL) {
		  //fprintf(stderr, "3 SQL error: %s\n", err);
		  sqlite3_free(err);
		}
	}	
	sql = "CREATE TABLE playlists(key INTEGER PRIMARY KEY, name text, artist text);";
	db_ret = sqlite3_exec(songs, sql, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
		if (err != NULL) {
		  //fprintf(stderr, "4 SQL error: %s\n", err);
		  sqlite3_free(err);
		}
	}
	// add default values
	sql ="INSERT INTO playlists(name) VALUES('0 All Songs');";
	db_ret = sqlite3_exec(songs, sql, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
		if (err != NULL) {
		  //fprintf(stderr, "5 SQL error: %s\n", err);
		  sqlite3_free(err);
		}
	}
	sql = "CREATE TABLE '0 All Songs'(key integer primary key, song text, artist text, duration integer, path text, album text);";
	db_ret = sqlite3_exec(songs, sql, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
		if (err != NULL) {
		  //fprintf(stderr, "6 SQL error: %s\n", err);
		  sqlite3_free(err);
		}
	}
	sql = "CREATE TABLE 'ty_playlist'(key integer primary key, song text, path text);";
	db_ret = sqlite3_exec(songs, sql, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
		if (err != NULL) {
		  //fprintf(stderr, "7 SQL error: %s\n", err);
		  sqlite3_free(err);
		}
	}
	sql = "CREATE TABLE 'song_dirs'(key integer primary key, dir text)";
	db_ret = sqlite3_exec(songs, sql, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
		if (err != NULL) {
		  //fprintf(stderr, "8 SQL error: %s\n", err);
		  sqlite3_free(err);
		}
	}
	// add default values
	sql = "INSERT INTO state(playlist, song_no, volume, seek, dur, loop, finger, random, bass, treble, priority, version, bt) VALUES('Select a Playlist!', 0, 80, 0, 0, 0, 0, 0, 0, 0, 0, 63, 0)";
	db_ret = sqlite3_exec(songs, sql, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
		if (err != NULL) {
		  //fprintf(stderr, "9 SQL error: %s\n", err);
		  sqlite3_free(err);
		}
	}
	//set up environment values
	sprintf(cur_playlist, "Select a Playlist!");
	song_index = 0;
	vol = 80;
	view = LISTVIEW;
	version = 63;
}

void modify_state(void) 
{
	char *sql, *err;
	int db_ret;
	const char  *tail;
	sqlite3_stmt *stmt;
	
	//get the songs_dir
	sql = "SELECT songs_dir FROM state";
	db_ret = sqlite3_prepare(songs, sql, strlen(sql), &stmt, &tail);
	if(db_ret != SQLITE_OK) {
		//printf("10 SQL error: %d %s\n", db_ret, sqlite3_errmsg(songs));
	}
	else {
		db_ret = sqlite3_step(stmt);
		if (db_ret == SQLITE_ROW) {
			if (sqlite3_column_text(stmt, 0)) sprintf(songs_dir, "%s", sqlite3_column_text(stmt, 0));
			else strcpy(songs_dir, "Select a Playlist!");
		}
	}
	sqlite3_finalize(stmt);
	
	//create song_dirs table
	sql = "CREATE TABLE 'song_dirs'(key integer primary key, dir text)";
	db_ret = sqlite3_exec(songs, sql, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
		if (err != NULL) {
		  //fprintf(stderr, "11 SQL error: %s\n", err);
		  sqlite3_free(err);
		}
	}
	//insert song directory in new table
	sql = sqlite3_mprintf("INSERT INTO song_dirs(dir) VALUES('%q')", songs_dir);
	db_ret = sqlite3_exec(songs, sql, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
	  if (err != NULL) {
		  //fprintf(stderr, "12 SQL error: %s\n", err);
		  sqlite3_free(err);
	  }
	}
	sqlite3_free(sql);

	sqlite3_exec(songs, "BEGIN", 0, 0, 0);
	//modify state table
	sql ="DROP TABLE state";
	db_ret = sqlite3_exec(songs, sql, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
		if (err != NULL) {
		  //fprintf(stderr, "13 SQL error: %s\n", err);
		  sqlite3_free(err);
		}
	}
	sql ="CREATE TABLE state(key integer primary key, playlist text, song_no integer, volume integer, seek integer, dur integer, loop integer, finger integer, random integer, bass integer, treble integer, priority integer, version integer, bt integer)";
	db_ret = sqlite3_exec(songs, sql, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
		if (err != NULL) {
		  //fprintf(stderr, "14 SQL error: %s\n", err);
		  sqlite3_free(err);
		}
	}	
	sql = sqlite3_mprintf("INSERT INTO state(playlist, song_no, volume, seek, dur, loop, finger, random, bass, treble, priority, version, bt) VALUES('%q', %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d);",
							 cur_playlist, song_index, vol, seek, song_dur, loop, finger, randomize, bass, treble, priority, 63, bt);
	db_ret = sqlite3_exec(songs, sql, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
	  if (err != NULL) {
		  //fprintf(stderr, "15 SQL error: %s\n", err);
		  sqlite3_free(err);
	  }
	}
	sqlite3_free(sql);
	sqlite3_exec(songs, "COMMIT", 0, 0, 0);
	version = 63;
}

void restore_state(void)
{
	int db_ret, ret;
	char *sql, volume[255], db[255], *ans, line[255];
	const char  *tail;
	sqlite3_stmt *stmt;
	FILE *fp;
	
	//set intone directory
	sprintf(intone_dir, "/usr/share/intone");
	//get the users home directory
	const char *name = "HOME";
	sprintf(home_dir, "%s", getenv(name));
	sprintf(db, "%s/.intone/intone_songs.db", home_dir);
	//connect to the db
	db_ret = sqlite3_open(db, &songs);
	
	if (db_ret == SQLITE_ERROR || db_ret == SQLITE_CANTOPEN) {
		//printf("16 SQL error: %s\n", sqlite3_errmsg(songs));
		//printf("Creating new db file\n");
		//exit(1);
		ret = system("mkdir ~/.intone");
		sqlite3_close(songs);
		db_ret = sqlite3_open(db, &songs);
	}
	//get the db version
	sql = "SELECT version FROM state";
	db_ret = sqlite3_prepare(songs, sql, strlen(sql), &stmt, &tail);
	if(db_ret != SQLITE_OK) {
		if (strcmp(sqlite3_errmsg(songs), "no such table: state")==0) first_run();
		//printf("16.5 SQL error: %d %s\n", db_ret, sqlite3_errmsg(songs));
	}
	else {
		db_ret = sqlite3_step(stmt);
		if (db_ret == SQLITE_ROW) {
			version = sqlite3_column_int(stmt, 0);
		}
	}
	sqlite3_finalize(stmt);
	//printf("db ver %d\n", version);
	if (version < 63) modify_state();
	
	//load the state data from state table
	sql = "SELECT *  FROM state";
	db_ret = sqlite3_prepare(songs, sql, strlen(sql), &stmt, &tail);
	if(db_ret != SQLITE_OK) {
		//printf("17 SQL error: %d %s\n", db_ret, sqlite3_errmsg(songs));
	}
	else {
		//set up environment variables
		while((db_ret = sqlite3_step(stmt)) == SQLITE_ROW) {
			sprintf(cur_playlist, "%s", sqlite3_column_text(stmt, 1));
			song_index = sqlite3_column_int(stmt, 2);
			//cater for sqlite saving -1 sometimes
			if (song_index < 0) song_index = 0;
			vol = sqlite3_column_int(stmt, 3);
			seek = sqlite3_column_int(stmt, 4);
			song_dur = sqlite3_column_int(stmt, 5);
			loop = sqlite3_column_int(stmt, 6);
			finger = sqlite3_column_int(stmt, 7);
			randomize = sqlite3_column_int(stmt, 8);
			bass = sqlite3_column_int(stmt, 9);
			treble = sqlite3_column_int(stmt, 10);
			priority = sqlite3_column_int(stmt, 11);
			version = sqlite3_column_int(stmt, 12);
			bt = sqlite3_column_int(stmt, 13);
		}
	}
	sqlite3_finalize(stmt);
	//for the first time set dynamic arrays to a value so that they can be freed by set_no_songs
	path_songs = calloc(1, sizeof(*path_songs));
	//song_list = calloc(1, sizeof(*song_list));
	pl_data = (_dir *) malloc(sizeof(_dir) * 1);
	//set up no_songs
	if ((strlen(cur_playlist) > 1) && (strcmp(cur_playlist, "Select a Playlist!") != 0)) set_no_songs();
	if(song_index > no_songs - 1) song_index = no_songs - 1;
	//set up the random sequence
	randomize_list();
	if(randomize) {
		next_song_random = 1;
		seek = 0;
		song_index = 0;
	}
	//get the sys_vol pcm
	fp = popen("amixer -c 0 get PCM", "r");
	while ((ans = fgets(line, sizeof line, fp))) {
		if (strncmp(ans, "  Front Right", 13) == 0) {
			char s_v[4];
			char *tok = strtok(ans, "[");
			tok = strtok(NULL, "[");
			strncpy(s_v, tok, 4);
			sys_vol = atoi(s_v);
		}
	}
	pclose(fp);
	//set the volume
	sprintf(volume, "amixer -qc 0 set PCM %d%s", vol, "\%\n");
	db_ret = system(volume);
}

void set_no_songs(void)
{
	int db_ret;
	char *sql;
	const char  *tail;
	sqlite3_stmt *stmt;
	
	sql = sqlite3_mprintf("SELECT COUNT(song) FROM '%q'", cur_playlist);
	db_ret = sqlite3_prepare(songs, sql, strlen(sql), &stmt, &tail);
	if(db_ret != SQLITE_OK) {
		//printf("18 SQL error: %s\n", sqlite3_errmsg(songs));
	}
	db_ret = sqlite3_step(stmt);
	if (db_ret == SQLITE_ROW) no_songs = sqlite3_column_int(stmt, 0);
	else {
		//printf("19 SQL error: %s\n", sqlite3_errmsg(songs));
	}
	sqlite3_free(sql);
	sqlite3_finalize(stmt);
	//free dynamic arrays
	free(path_songs);
	//free(song_list);
	free(pl_data);
	//set up the dynamic arrays for path_songs and song_list
	path_songs = calloc(no_songs, sizeof(*path_songs));
	//song_list = calloc(no_songs, sizeof(*song_list));
	pl_data = (_dir *) malloc(sizeof(_dir) * no_songs);
}

int add_playlist(char *playlist)
{
	int db_ret, num;
	char *err, *sql_st=NULL;
	const char  *tail;
	sqlite3_stmt *stmt;
	
	//check whether playlist already exists 
	sql_st = sqlite3_mprintf("SELECT COUNT(name) FROM playlists where name = '%q';", playlist);
	db_ret = sqlite3_prepare(songs, sql_st, strlen(sql_st), &stmt, &tail);
	if(db_ret != SQLITE_OK) {
		//printf("20 SQL error: %s\n", sqlite3_errmsg(songs));
	}
	db_ret = sqlite3_step(stmt);
	if (db_ret == SQLITE_ROW) num = sqlite3_column_int(stmt, 0);
	else {
		//printf("21 SQL error: %s\n", sqlite3_errmsg(songs));
		return 2;
	}
	sqlite3_free(sql_st);
	sqlite3_finalize(stmt);

	//if playlist exists - empty it for rescan
	if (num) {
		// empty playlist 
		sql_st = sqlite3_mprintf("DELETE FROM '%q';", playlist);
		db_ret = sqlite3_exec(songs, sql_st, NULL, NULL, &err);
		if (db_ret != SQLITE_OK) {
		  if (err != NULL) {
			  //fprintf(stderr, "22 SQL error: %s\n", err);
			  sqlite3_free(err);
		  }
		}
		sqlite3_free(sql_st);
		return 0;
	}
	else {
		//add playlist
		sql_st = sqlite3_mprintf("INSERT INTO playlists(name) VALUES('%q');", playlist);
		db_ret = sqlite3_exec(songs, sql_st, NULL, NULL, &err);
		if (db_ret != SQLITE_OK) {
		  if (err != NULL) {
			  //fprintf(stderr, "23 SQL error: %s\n", err);
			  sqlite3_free(err);
		  }
		}
		sqlite3_free(sql_st);

		sql_st = sqlite3_mprintf("CREATE TABLE '%q'(key INTEGER PRIMARY KEY, song text, artist text, duration integer, path text, album text);", playlist);
		db_ret = sqlite3_exec(songs, sql_st, NULL, NULL, &err);
		if (db_ret != SQLITE_OK) {
		  if (err != NULL) {
			  //fprintf(stderr, "24 SQL error: %s\n", err);
			  sqlite3_free(err);
		  }
		}
		sqlite3_free(sql_st);
		return 1;
	}
}

void begin_transaction(void)
{
	sqlite3_exec(songs, "BEGIN", 0, 0, 0);
}

void commit_transaction(void)
{
	sqlite3_exec(songs, "COMMIT", 0, 0, 0);
}

void get_song_tag_mp3(char *filename)
{
	char file_nm[510];
	int len;
	ID3Tag *tag;
	
	if ((tag = ID3Tag_New()) != NULL) {
		ID3Tag_LinkWithFlags(tag, filename, ID3TT_ID3V1); //patch by Steven **-4
		ID3TagIterator *iter=NULL;
		ID3Frame *frame;
		ID3_FrameID id;
		
		while (NULL != (frame = ID3TagIterator_GetNext(iter))) {
			id = ID3Frame_GetID(frame);
			ID3Field *field;
			char tag_data[1024];
			switch(id) {
				case ID3FID_TITLE : if ((field = ID3Frame_GetField(frame, ID3FN_TEXT)) != NULL) {
										ID3Field_GetASCII(field, tag_data, 1024);
										strcpy(tag_song, tag_data);
									}
									else strcpy(tag_song, "");
									break;

				case ID3FID_LEADARTIST : if ((field = ID3Frame_GetField(frame, ID3FN_TEXT)) != NULL) {
											ID3Field_GetASCII(field, tag_data, 1024);
											strcpy(tag_artist, tag_data);
										 }
										 else strcpy(tag_artist, "");
										 break;
				
				case ID3FID_ALBUM : if ((field = ID3Frame_GetField(frame, ID3FN_TEXT)) != NULL) {
										ID3Field_GetASCII(field, tag_data, 1024);
										strcpy(tag_album, tag_data);
									}
									else strcpy(tag_album, "");
									break;

				case ID3FID_PICTURE : //get type of pic
									if ((field = ID3Frame_GetField(frame, ID3FN_MIMETYPE)) != NULL) {
										ID3Field_GetASCII(field, tag_data, 1024);
									}
									//printf("%s\n", tag_data);
									strcpy(file_nm, filename);
									len = strlen(file_nm);
									if(strcmp(tag_data, "image/jpeg") == 0) {
										file_nm[len-3] = 'j';
										file_nm[len-2] = 'p';
										file_nm[len-1] = 'g';
									}
									else if(strcmp(tag_data, "image/png") == 0) {
										file_nm[len-3] = 'p';
										file_nm[len-2] = 'n';
										file_nm[len-1] = 'g';
									}
								    //save image as filename . pic type
									if ((field = ID3Frame_GetField(frame, ID3FN_DATA)) != NULL) {
										ID3Field_ToFile(field, file_nm);
									}
									break;
				
				default : break;
			}
		}
		ID3TagIterator_Delete(iter);
	}
	else {
		strcpy(tag_artist, "");
		strcpy(tag_song, "");
		strcpy(tag_album, "");
	}
}

/*void get_song_tag_mp3(char *filename)
{
	ID3Tag *tag;
	if ((tag = ID3Tag_New()) != NULL) {
		ID3Frame *frame;
		(void) ID3Tag_LinkWithFlags(tag, filename, ID3TT_ID3V1); //patch by Steven **-4
		if ((frame = ID3Tag_FindFrameWithID(tag, ID3FID_TITLE)) != NULL) {
			ID3Field *field;
			if ((field = ID3Frame_GetField(frame, ID3FN_TEXT)) != NULL) {
				char title[1024];
				(void) ID3Field_GetASCII(field, title, 1024);
				strcpy(tag_song, title);
			}
			else strcpy(tag_song, "");
		}
		else strcpy(tag_song, "");
		if ((frame = ID3Tag_FindFrameWithID(tag, ID3FID_LEADARTIST)) != NULL) {
			ID3Field *field;
			if ((field = ID3Frame_GetField(frame, ID3FN_TEXT)) != NULL) {
				char artist[1024];
				(void) ID3Field_GetASCII(field, artist, 1024);
				strcpy(tag_artist, artist);
			}
			else strcpy(tag_artist, "");
		}
		else strcpy(tag_artist, "");
		if ((frame = ID3Tag_FindFrameWithID(tag, ID3FID_ALBUM)) != NULL) {
			ID3Field *field;
			if ((field = ID3Frame_GetField(frame, ID3FN_TEXT)) != NULL) {
				char album[1024];
				(void) ID3Field_GetASCII(field, album, 1024);
				strcpy(tag_album, album);
			}
			else strcpy(tag_album, "");
		}
		else strcpy(tag_album, "");
	}
	else {
		strcpy(tag_artist, "");
		strcpy(tag_song, "");
		strcpy(tag_album, "");
	}
}*/

void get_song_tag_ogg (char *filename)
{
	OggVorbis_File vf;
	FILE *ogg_file;
	int i,j;
	char *ok, *tystr;
	
	ogg_file = fopen(filename, "r");
	
	if(ov_open(ogg_file, &vf, NULL, 0) < 0) {
    	//printf("Input does not appear to be an Ogg bitstream.\n");
    	return;
	}

	char **ptr=ov_comment(&vf,-1)->user_comments;
	i = ov_comment(&vf,-1)->comments;
	for(j=0; j<i; j++) {
		if((ok=strstr(ptr[j], "TITLE"))) {
			tystr = strtok(ptr[j], "=");
			tystr = strtok(NULL, "=");
			if (tystr) strcpy(tag_song, tystr);
		}
		if((ok=strstr(ptr[j], "ALBUM"))) {
			tystr = strtok(ptr[j], "=");
			tystr = strtok(NULL, "=");
			if (tystr) strcpy(tag_album, tystr);
		}
		if((ok=strstr(ptr[j], "ARTIST"))) {
			tystr = strtok(ptr[j], "=");
			tystr = strtok(NULL, "=");
			if (tystr) strcpy(tag_artist, tystr);
		}
	}
}

char *clean_string(char *str)
{
	int i;
	char *tystr, clean[1024], dirty[1024], val[1024];
	size_t ty, len=0;
	
	strcpy(dirty, str);
	//remove trailing spaces and apostrophe
	ty = strlen(dirty) - 2;
	for(i=ty;i>0;i--) {
		if(dirty[i] ==' ' || dirty[i] == '\'') len ++;
		else break;
	}
	strncpy(clean, dirty, ty - len + 1);
	
	clean[ty - len + 1] = '\0';
	strcpy(dirty, clean);
	tystr = dirty;

	//remove leading spaces and apostrophe
	for(i=0; i<ty;i++) {
		if(dirty[i] ==' ' || dirty[i] == '\'') tystr ++;
		else break;
	}
	//TODO:replace & with html safe character
	strcpy(val, tystr);
	tystr = val;
	return tystr;
}

void add_songs(char *playlist, char* song, char *path)
{
	int db_ret;
	char  *err, *sql_st=NULL, *artist=NULL, *album=NULL, *ok=NULL;

	if ((ok = strstr(path, ".mp3")) || (ok = strstr(path, ".MP3"))) get_song_tag_mp3(path);
	if ((ok = strstr(path, ".ogg")) || (ok = strstr(path, ".OGG"))) get_song_tag_ogg(path);
	if (strcmp(tag_song, "") !=0) song =  tag_song;
	if (strcmp(tag_artist, "") !=0) artist = tag_artist;
	if (strcmp(tag_album, "") !=0) album =  tag_album;

	//insert data into the table
	sql_st = sqlite3_mprintf("INSERT INTO '%q'(song, artist, duration, path, album) VALUES('%q', '%q', 0,  '%q', '%q');", playlist, song, artist, path, album);
	db_ret = sqlite3_exec(songs, sql_st, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
	  if (err != NULL) {
		  //fprintf(stderr, "25 SQL error: %s\n", err);
  		  //printf("%s\n", sql_st);
		  sqlite3_free(err);
	  }
	}
	sqlite3_free(sql_st);
	//set tag values to null
	strcpy(tag_song, "");
	strcpy(tag_album, "");
	strcpy(tag_artist, "");
}

void update_album_list(Elm_Genlist_Item_Class *cl, Evas_Object *li)
{
	int db_ret;
	char *sql, *alb_name;
	const char  *tail;
	sqlite3_stmt *stmt;
	char album[255];

	sql = "SELECT name FROM playlists";	
	db_ret = sqlite3_prepare(songs, sql, strlen(sql), &stmt, &tail);
	if(db_ret != SQLITE_OK) {
		//printf("26 SQL error: %s\n", sqlite3_errmsg(songs));
	}
	while((db_ret = sqlite3_step(stmt)) == SQLITE_ROW) {
		sprintf(album, "%s", sqlite3_column_text(stmt, 0));
		alb_name = strdup(album);
		elm_genlist_item_append(li, cl, (char *)alb_name,  NULL, 
																					  ELM_GENLIST_ITEM_NONE,
																					  NULL, 
																					  NULL);
	}
	sqlite3_finalize(stmt);
}

void add_default_playlist(void)
{
	int db_ret;
	char *err, *sql_stmt, *sql_stmt_1=NULL, *sql_stmt_2=NULL, name[255];
	const char  *tail, *tail_1;
	sqlite3_stmt *stmt, *stmt_1;

	//empty default playlist
	sql_stmt = "DELETE FROM '0 All Songs'";
	db_ret = sqlite3_exec(songs, sql_stmt, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
	  if (err != NULL) {
		  //fprintf(stderr, "27 SQL error: %s\n", err);
		  sqlite3_free(err);
	  }
	}

	//get playlist name
	sql_stmt = "SELECT name FROM playlists";	
	db_ret = sqlite3_prepare(songs, sql_stmt, strlen(sql_stmt), &stmt, &tail);
	if(db_ret != SQLITE_OK) {
		//printf("SQL error: %s\n", sqlite3_errmsg(songs));
	}
	while((db_ret = sqlite3_step(stmt)) == SQLITE_ROW) {
		sprintf(name, "%s", sqlite3_column_text(stmt, 0));
		//get data from each playlist and add to default playlist
		sql_stmt_1 = sqlite3_mprintf("SELECT * FROM '%q';", name);
		db_ret = sqlite3_prepare(songs, sql_stmt_1, strlen(sql_stmt_1), &stmt_1, &tail_1);
		if(db_ret != SQLITE_OK) {
			//printf("28 SQL error: %s\n", sqlite3_errmsg(songs));
		}
		while((db_ret = sqlite3_step(stmt_1)) == SQLITE_ROW) {
			//insert data into the default playlist
			sql_stmt_2 = sqlite3_mprintf("INSERT INTO '0 All Songs'(song, artist, duration, path, album) VALUES('%q', '%q', %d, '%q', '%q');", 
			        sqlite3_column_text(stmt_1, 1), sqlite3_column_text(stmt_1, 2), sqlite3_column_int(stmt_1, 3), sqlite3_column_text(stmt_1, 4), name);
			db_ret = sqlite3_exec(songs, sql_stmt_2, NULL, NULL, &err);
			if (db_ret != SQLITE_OK) {
			  if (err != NULL) {
				  //fprintf(stderr, "29 SQL error: %s\n", err);
				  sqlite3_free(err);
			  }
			}
			sqlite3_free(sql_stmt_2);
		}
		sqlite3_free(sql_stmt_1);
		sqlite3_finalize(stmt_1);
	}
	sqlite3_finalize(stmt);
}

void populate_play_list(Evas_Object *li)
{
	int db_ret;
	char *sql, *pl_name, album[255];
	const char  *tail;
	sqlite3_stmt *stmt;
	Elm_Genlist_Item *gl_item=NULL;

	elm_genlist_clear(li);
	sql = "SELECT name FROM playlists";
	db_ret = sqlite3_prepare(songs, sql, strlen(sql), &stmt, &tail);
	if(db_ret != SQLITE_OK) {
		//printf("30 SQL error: %s\n", sqlite3_errmsg(songs));
	}
	while((db_ret = sqlite3_step(stmt)) == SQLITE_ROW) {
		sprintf(album, "%s", sqlite3_column_text(stmt, 0));
		pl_name = strdup(album);
		if (strcmp(cur_playlist, album) ==0) gl_item = elm_genlist_item_append
			(li, &itc_play_list, (char *)pl_name, NULL, ELM_GENLIST_ITEM_NONE, NULL, NULL);
		else elm_genlist_item_append(li, &itc_play_list, (char *)pl_name, NULL, 
		                        ELM_GENLIST_ITEM_NONE, NULL, NULL);
	}
	sqlite3_finalize(stmt);
	//select the current playlist / first playlist
	if (gl_item == NULL) {
		gl_item = elm_genlist_first_item_get(li);
	}
	elm_genlist_item_selected_set(gl_item ,1);
	elm_genlist_item_show(gl_item);
}

void populate_playlist_songs(Evas_Object *li)
{
	int db_ret;
	char *sql, playlist_name[255], path[255], *pa, song[255];
	const char  *tail;
	sqlite3_stmt *stmt;
	
	//get name of selected playlist from play_list
	Elm_Genlist_Item *it = (Elm_Genlist_Item *)elm_genlist_selected_item_get(play_list);
	strcpy(playlist_name, (char *)elm_genlist_item_data_get(it));
	//clear the playlist_songs list
	elm_genlist_clear(li);
	//get data for this playlist and add to playlist_songs
	sql =  sqlite3_mprintf("SELECT song, path  FROM '%q'", playlist_name);
	db_ret = sqlite3_prepare(songs, sql, strlen(sql), &stmt, &tail);
	if(db_ret != SQLITE_OK) {
		//printf("31 SQL error: %s\n", sqlite3_errmsg(songs));
	}
	while((db_ret = sqlite3_step(stmt)) == SQLITE_ROW) {
		sprintf(song, "%s", sqlite3_column_text(stmt, 0));
		sprintf(path, "%s", sqlite3_column_text(stmt, 1));
		pa = strdup(path);
		elm_genlist_item_append(li, &itc_c_play_list, (char *)pa, NULL, ELM_GENLIST_ITEM_NONE,
		                        NULL, NULL);
	}
	sqlite3_finalize(stmt);
	sqlite3_free(sql);
}

void sel_song(void)
{
	Elm_Genlist_Item *song_to_play=NULL;

	if(song_list[song_index]) {
		if (!randomize) song_to_play = song_list[song_index];
		else song_to_play = song_list[seq[song_index]];
		if (song_to_play) {
			elm_genlist_item_selected_set(song_to_play, 1);
			old_song = song_to_play;
			elm_genlist_item_show(song_to_play);
		}
	}
}

void populate_main_list(Evas_Object *li)
{
	int db_ret, i=0;
	char *sql, song_name[255];
	const char *tail;
	sqlite3_stmt *stmt;
		
	//clear the list
	elm_genlist_clear(list);
	//if playlist name is "" add a statement saying add Music first
	if ((strlen(cur_playlist) < 1) || (strcmp(cur_playlist, "Select a Playlist!") == 0)) {
		strcpy(pl_data[0].path, "Add Songs using the 'Songs' Button!");
		pl_data[0].type = i;
		elm_genlist_item_append(li, &itc_list, &pl_data[0], 
                                      NULL, ELM_GENLIST_ITEM_NONE, NULL, NULL);
		return;
	}
	//otherwise use the cur playlist
	if (strcmp(order_by, "none") == 0) sql = sqlite3_mprintf("SELECT song, path FROM '%q'", cur_playlist);
	else sql = sqlite3_mprintf("SELECT song, path FROM '%q' ORDER BY '%q'", cur_playlist, order_by);
	db_ret = sqlite3_prepare(songs, sql, strlen(sql), &stmt, &tail);
	if(db_ret != SQLITE_OK) {
		//printf("32 SQL error: %s\n", sqlite3_errmsg(songs));
	}
	while((db_ret = sqlite3_step(stmt)) == SQLITE_ROW) {
		sprintf(song_name, "%s", sqlite3_column_text(stmt, 0));
		sprintf(path_songs[i], "%s", sqlite3_column_text(stmt, 1));
		strcpy(pl_data[i].path, song_name);
		pl_data[i].type = i;
		song_list[i] = elm_genlist_item_append(list, &itc_list, 
       										  &pl_data[i],
											  NULL, 
											  ELM_GENLIST_ITEM_NONE,
											  NULL, 
											  NULL);
		if(!(check_file_exists (path_songs[i]))) 	
			elm_genlist_item_disabled_set(song_list[i], 1);
		i ++;
	}
	sqlite3_free(sql);
	sqlite3_finalize(stmt);
	
	//cater for a case where playlist has no data
	if (!i){
		elm_genlist_item_append(list, &itc_list, 
                                      "Add Songs using the 'Songs' Button!", 
                                      NULL, ELM_GENLIST_ITEM_NONE, NULL, NULL);
		elm_genlist_item_selected_set(elm_genlist_first_item_get(list), 1);
		no_songs = 1;
		song_index = 0;
		return;
	}
	no_songs = i;
	sel_song();
}

int get_song_pos(char *path)
{
	char *sql;
	int db_ret, pos=0;
	const char  *tail;
	sqlite3_stmt *stmt;

	sql = sqlite3_mprintf("SELECT key FROM '%q' WHERE path = '%q';", cur_playlist, path);
	db_ret = sqlite3_prepare(songs, sql, strlen(sql), &stmt, &tail);
	if(db_ret != SQLITE_OK){} //printf("33 SQL error: %s\n", sqlite3_errmsg(songs));
	else {
		db_ret = sqlite3_step(stmt);
		pos = sqlite3_column_int(stmt, 0);
	}
	sqlite3_finalize(stmt);
	sqlite3_free(sql);
	return pos;
}

void del_playlist(char *list)
{
	int db_ret;
	char  *err, *sql_st, li[255];

	sprintf(li, "%s", list);
	sql_st = sqlite3_mprintf("DROP TABLE '%q'", li);
	db_ret = sqlite3_exec(songs, sql_st, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
	  if (err != NULL) {
		  //fprintf(stderr, "34 SQL error: %s\n", err);
		  sqlite3_free(err);
	  }
	}
	sqlite3_free(sql_st);

	sql_st = sqlite3_mprintf("DELETE FROM playlists WHERE name = '%q'", list);
	db_ret = sqlite3_exec(songs, sql_st, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
	  if (err != NULL) {
		  //fprintf(stderr, "35 SQL error: %s\n", err);
		  sqlite3_free(err);
	  }
	}
	sqlite3_free(sql_st);

	sql_st = sqlite3_mprintf("DELETE FROM '0 All Songs' WHERE album = '%q'", list);
	db_ret = sqlite3_exec(songs, sql_st, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
	  if (err != NULL) {
		  //fprintf(stderr, "35.5 SQL error: %s\n", err);
		  sqlite3_free(err);
	  }
	}
	sqlite3_free(sql_st);
}

void save_playlist (char *playlist, Evas_Object *li)
{
	int db_ret, num;
	char  *err, *sql_st, *name;
	const Elm_Genlist_Item *item;
	sqlite3_stmt *stmt;
	const char *tail;

	//check playlist exists
	sql_st = sqlite3_mprintf("SELECT count(name) FROM playlists WHERE NAME = '%q';", playlist);
	db_ret = sqlite3_prepare(songs, sql_st, strlen(sql_st), &stmt, &tail);
	//if(db_ret != SQLITE_OK) printf("36 SQL error: %s\n", sqlite3_errmsg(songs));
	db_ret = sqlite3_step(stmt);
	num = sqlite3_column_int(stmt, 0);
	sqlite3_finalize(stmt);
	sqlite3_free(sql_st);

	if (num == 0) {
		//add name to playlists
		sql_st = sqlite3_mprintf("INSERT INTO playlists(name) VALUES('%q');", playlist);
		db_ret = sqlite3_exec(songs, sql_st, NULL, NULL, &err);
		if (db_ret != SQLITE_OK) {
		  if (err != NULL) {
			  //fprintf(stderr, "37 SQL error: %s\n", err);
			  sqlite3_free(err);
		  }
		}
		sqlite3_free(sql_st);
		//create a new table
		sql_st = sqlite3_mprintf("CREATE TABLE '%q'(key INTEGER PRIMARY KEY, song text, artist text, duration integer, path text, album text);", playlist);
		db_ret = sqlite3_exec(songs, sql_st, NULL, NULL, &err);
		if (db_ret != SQLITE_OK) {
		  if (err != NULL) {
			  //fprintf(stderr, "38 SQL error: %s\n", err);
			  sqlite3_free(err);
		  }
		}
		sqlite3_free(sql_st);
	}
	else {
		//delete data from the table
		sql_st = sqlite3_mprintf("DELETE FROM '%q';", playlist);
		db_ret = sqlite3_exec(songs, sql_st, NULL, NULL, &err);
		if (db_ret != SQLITE_OK) {
		  if (err != NULL) {
			  //fprintf(stderr, "39 SQL error: %s\n", err);
			  sqlite3_free(err);
		  }
		}
		sqlite3_free(sql_st);
	}
	//add data to new table
	item = elm_genlist_first_item_get(li);
	while(item) {
		char *path = (char *)elm_genlist_item_data_get(item);
		name = strrchr(path,  '/');
		name ++;
		//add song album value also
		sql_st = sqlite3_mprintf("INSERT INTO '%q'(song, path) VALUES('%q', '%q');", playlist, name, path);
		db_ret = sqlite3_exec(songs, sql_st, NULL, NULL, &err);
		if (db_ret != SQLITE_OK) {
		  if (err != NULL) {
			  //fprintf(stderr, "40 SQL error: %s\n", err);
			  sqlite3_free(err);
		  }
		}
		sqlite3_free(sql_st);
		if (path) free(path);
		item = elm_genlist_item_next_get(item);
	}
	elm_genlist_clear(li);
}

void add_songs_from_album(char *album, Evas_Object *list)
{
	int db_ret;
	char *sql, *pa, path[255];
	sqlite3_stmt *stmt;
	const char *tail;
	
	//get data from album
	sql =  sqlite3_mprintf("SELECT path  FROM '%q'", album);
	db_ret = sqlite3_prepare(songs, sql, strlen(sql), &stmt, &tail);
	if(db_ret != SQLITE_OK) {
		//printf("41 SQL error: %s\n", sqlite3_errmsg(songs));
	}
	while((db_ret = sqlite3_step(stmt)) == SQLITE_ROW) {
		sprintf(path, "%s", sqlite3_column_text(stmt, 0));
		pa = strdup(path);
		elm_genlist_item_append(list, &itc_c_play_list, (char *)pa, NULL,
		                        ELM_GENLIST_ITEM_NONE, NULL, NULL);
	}
	sqlite3_free(sql);
	sqlite3_finalize(stmt);
}

void populate_filter_list(char *type, Evas_Object *list)
{
	int db_ret;
	char *sql, *tystr, val[255];
	sqlite3_stmt *stmt;
	const char *tail;
	
	if (strcmp(type, "Artists") == 0) sql = "SELECT DISTINCT artist FROM '0 All Songs' ORDER BY artist";	
	else sql = "SELECT DISTINCT album FROM '0 All Songs' ORDER BY album";
	
	db_ret = sqlite3_prepare(songs, sql, strlen(sql), &stmt, &tail);
	if(db_ret != SQLITE_OK) {
		//printf("42 SQL error: %s\n", sqlite3_errmsg(songs));
	}
	while((db_ret = sqlite3_step(stmt)) == SQLITE_ROW) {
		if (sqlite3_column_text(stmt, 0)) sprintf(val, "%s", sqlite3_column_text(stmt, 0));
		if (strcmp(val, "(NULL)") != 0){
			tystr = strdup(val);
			elm_genlist_item_append(list, &itc_filter_list, tystr, NULL, ELM_GENLIST_ITEM_NONE, NULL, NULL);
		}
	}
	sqlite3_finalize(stmt);
}

void populate_filter_final_list(char *type, char *name, Evas_Object *list)
{
	//add song name and path to the list
	int db_ret, i;
	char *sql, val[255], path[255];
	sqlite3_stmt *stmt;
	const char *tail;
	
	if (strcmp(type, "Artists") == 0) sql = sqlite3_mprintf("SELECT song, path FROM '0 All Songs' WHERE artist = '%q'", name);	
	else sql = sqlite3_mprintf("SELECT song, path FROM '0 All Songs' WHERE album = '%q'", name);
	db_ret = sqlite3_prepare(songs, sql, strlen(sql), &stmt, &tail);
	if(db_ret != SQLITE_OK) {
		//printf("43 SQL error: %s\n", sqlite3_errmsg(songs));
	}
	i=0; //set counter to 0
	while((db_ret = sqlite3_step(stmt)) == SQLITE_ROW) {
		if (sqlite3_column_text(stmt, 0)) sprintf(val, "%s", sqlite3_column_text(stmt, 0));
		if (strcmp(val, "(NULL)") != 0){
			sprintf(path, "%s", sqlite3_column_text(stmt, 1));
			strcpy(name_path[i].name, val);
			strcpy(name_path[i].path , path);
			elm_genlist_item_append(list, &itc_filter_final, &name_path[i], NULL, ELM_GENLIST_ITEM_NONE, NULL, NULL);
			i++;
		}
	}
	sqlite3_free(sql);
	sqlite3_finalize(stmt);
}

void create_ty_playlist_table()
{
	char *sql, *err;
	int db_ret;

	sql = "CREATE TABLE 'ty_playlist'(key integer primary key, song text, path text);";
	db_ret = sqlite3_exec(songs, sql, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
		if (err != NULL) {
		  //fprintf(stderr, "44 SQL error: %s\n", err);
		  sqlite3_free(err);
		}
	}
}

void setup_ty_playlist(Evas_Object *li)
{
	char *sql, *err;
	int db_ret;
	const Eina_List *item;
	
	//TODO: does ty_playlist need anything more than song and path?
	//clear ty_playlist
	sql ="DELETE FROM ty_playlist;";
	db_ret = sqlite3_exec(songs, sql, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
		if (err != NULL) {
		  //fprintf(stderr, "45 SQL error: %s\n", err);
		  if(strcmp(err, "no such table: ty_playlist") == 0) create_ty_playlist_table();
		  sqlite3_free(err);
		}
	}
		
	while((item = elm_genlist_selected_items_get(li))) {
		_str2 *da = (_str2 *)elm_genlist_item_data_get(item->data);
		//add to the table
		sql = sqlite3_mprintf("INSERT INTO ty_playlist(song, path) VALUES('%q', '%q');", da->name, da->path);
		db_ret = sqlite3_exec(songs, sql, NULL, NULL, &err);
		if (db_ret != SQLITE_OK) {
			if (err != NULL) {
			  //fprintf(stderr, "46 SQL error: %s\n", err);
			  sqlite3_free(err);
			}
		}	
		elm_genlist_item_del(item->data);
	}
	sqlite3_free(sql);
}

void set_song_info(void)
{
	int db_ret;
	char *sql, *path, s_artist[255];
	sqlite3_stmt *stmt;
	const char *tail;
	
	//get data from the database about the song
	if (randomize) path = path_songs[seq[song_index]];
	else path = path_songs[song_index];
	
	sql = sqlite3_mprintf("SELECT song, artist FROM '%q' WHERE path = '%q'", cur_playlist, path);
	db_ret = sqlite3_prepare(songs, sql, strlen(sql), &stmt, &tail);
	if(db_ret != SQLITE_OK) {
		//printf("47 SQL error: %s\n", sqlite3_errmsg(songs));
	}
	db_ret = sqlite3_step(stmt);
	if (db_ret == SQLITE_ROW) {
		sprintf(cur_song, "%s", sqlite3_column_text(stmt, 0));
		if (sqlite3_column_text(stmt, 1)) {
			sprintf(s_artist, "%s", sqlite3_column_text(stmt, 1));
			if (strcmp(s_artist, "(NULL)") == 0) strcpy(cur_artist, "");
			else strcpy(cur_artist, s_artist);
		}
	}
	sqlite3_free(sql);
	sqlite3_finalize(stmt);
}

void populate_home_list(Evas_Object *li)
{
	int db_ret;
	char *sql, path[255], *pa;
	sqlite3_stmt *stmt;
	const char *tail;

	elm_genlist_clear(li);
	sql = "SELECT dir FROM song_dirs";	
	db_ret = sqlite3_prepare(songs, sql, strlen(sql), &stmt, &tail);
	if(db_ret != SQLITE_OK) {
		//printf("48 SQL error: %s\n", sqlite3_errmsg(songs));
	}
	while((db_ret = sqlite3_step(stmt)) == SQLITE_ROW) {
		if(sqlite3_column_text(stmt, 0)) sprintf(path, "%s", sqlite3_column_text(stmt, 0));
		pa = strdup(path);
		elm_genlist_item_append(li, &itc_home_list, (char *)pa, NULL, ELM_GENLIST_ITEM_NONE, NULL, NULL);
	}
	sqlite3_finalize(stmt);
}

void add_song_dir(char *path)
{
	int db_ret, add=0;
	char *sql, *err;
	sqlite3_stmt *stmt;
	const char *tail;
	
	sql = sqlite3_mprintf("SELECT count(dir) FROM song_dirs WHERE dir='%q'", path);	
	db_ret = sqlite3_prepare(songs, sql, strlen(sql), &stmt, &tail);
	if(db_ret != SQLITE_OK) {
		//printf("48.5 SQL error: %s\n", sqlite3_errmsg(songs));
	}
	while((db_ret = sqlite3_step(stmt)) == SQLITE_ROW) {
		if(sqlite3_column_int(stmt, 0) == 0) add=1;
	}
	sqlite3_finalize(stmt);

	if(add) {
		sql = sqlite3_mprintf("INSERT INTO song_dirs(dir) VALUES('%q')", path);
		db_ret = sqlite3_exec(songs, sql, NULL, NULL, &err);
		if (db_ret != SQLITE_OK) {
			if (err != NULL) {
			  //fprintf(stderr, "49 SQL error: %s\n", err);
			  sqlite3_free(err);
			}
		}
		sqlite3_free(sql);
	}
}

int remove_song_dir(char *path)
{
	char *sql, *sql1, name[255], *err;
	int db_ret, co, n_pl=0, i=0;
	sqlite3_stmt *stmt, *stmt1;
	const char *tail, *tail1;

	//get number of playlists as max no that might need to be deleted
	sql = "SELECT count(name) FROM playlists";
	db_ret = sqlite3_prepare(songs, sql, strlen(sql), &stmt, &tail);
	if(db_ret != SQLITE_OK) {
		//printf("50 SQL error: %s\n", sqlite3_errmsg(songs));
	}
	else {
		db_ret = sqlite3_step(stmt);
		if (db_ret == SQLITE_ROW) {
			n_pl = sqlite3_column_int(stmt, 0);
		}
	}
	sqlite3_finalize(stmt);
	//make the array of max size
	del_pl = calloc(n_pl, sizeof(*del_pl));
	
	//get playlist names from playlists
	sql = "SELECT name FROM playlists";
	db_ret = sqlite3_prepare(songs, sql, strlen(sql), &stmt, &tail);
	if(db_ret != SQLITE_OK) {
		//printf("51 SQL error: %s\n", sqlite3_errmsg(songs));
	}
	while((db_ret = sqlite3_step(stmt)) == SQLITE_ROW) {
		if(sqlite3_column_text(stmt, 0)) sprintf(name, "%s", sqlite3_column_text(stmt, 0));
		//select path from this playlist 
		sql1 = sqlite3_mprintf("SELECT COUNT(path) FROM '%q' WHERE path LIKE '%q%%'", name, path);
		db_ret = sqlite3_prepare(songs, sql1, strlen(sql1), &stmt1, &tail1);
		if(db_ret != SQLITE_OK) {
			//printf("52 SQL error: %s\n", sqlite3_errmsg(songs));
		}
		else {
			db_ret = sqlite3_step(stmt1);
			if (db_ret == SQLITE_ROW) {
				co = sqlite3_column_int(stmt1, 0);
				if (co) {
					//populate the dynamic array with the playlist names
					strcpy(del_pl[i], name);
					i++;
				}//co
			}//db_ret
		}//else
		sqlite3_finalize(stmt1);
		sqlite3_free(sql1);
	}
	sqlite3_finalize(stmt);
	//remove this entry from song_dirs
	sql1 = sqlite3_mprintf("DELETE FROM song_dirs WHERE dir = '%q'", path);
	db_ret = sqlite3_exec(songs, sql1, NULL, NULL, &err);
	if (db_ret != SQLITE_OK) {
		if (err != NULL) {
		  //fprintf(stderr, "53 SQL error: %s\n", err);
		  sqlite3_free(err);
		}
	}
	sqlite3_free(sql1);
	return i;
}
